--将IDENTITY_INSERT状态设置为ON并插入一行包含标识列的数据
SET IDENTITY_INSERT users ON INSERT INTO users(uid,uname,sex,birth) VALUES(7,'uname','sex','1601-01-01')
--声明游标c1并使用FOR UPDATE定义可修改的列
DECLARE c1 CURSOR LOCAL SCROLL FOR SELECT * FROM users FOR UPDATE OF uname,birth
--声明变量以存储FETCH返回的值
DECLARE @uid AS INT
DECLARE @uname AS NVARCHAR(MAX)
DECLARE @sex AS NVARCHAR(MAX)
DECLARE @birth AS DATE
--打开游标
OPEN c1
/*获取SELECT查询语句结果集中第一行的记录并将各个列的值分别赋予各个变量*/
FETCH ABSOLUTE 7 FROM c1 INTO @uid,@uname,@sex,@birth
PRINT CONVERT(VARCHAR,@uid)+CHAR(9)+@uname+CHAR(9)+@sex+CHAR(9)+CONVERT(VARCHAR,@birth)
--使用游标更新当前行的指定列的值
UPDATE users SET uname='uname2',birth='1970-01-01' WHERE CURRENT OF c1
FETCH LAST FROM c1
DELETE users WHERE CURRENT OF c1--删除当前行的记录（删除时须确保其他表没有引用该表的外键）
FETCH ABSOLUTE 1 FROM c1--再次返回第1行
--关闭游标
CLOSE c1
--删除游标
DEALLOCATE c1
GO
--声明游标c2
DECLARE c2 CURSOR LOCAL SCROLL FOR SELECT * FROM courses
OPEN c2
DECLARE @cid INT
DECLARE @cname AS NVARCHAR(MAX)
DECLARE @n AS INT=0
--@@CURSOR_ROWS表示当前打开的游标中的行数目
WHILE @n<@@CURSOR_ROWS BEGIN
FETCH NEXT FROM c2 INTO @cid,@cname
PRINT CONVERT(VARCHAR,@cid)+CHAR(9)+@cname
SET @n+=1
END
CLOSE c2
DEALLOCATE c2
GO
--声明游标c3
DECLARE c3 CURSOR LOCAL SCROLL FOR SELECT * FROM scores
DECLARE @uid INT
DECLARE @cid INT
DECLARE @score AS INT
DECLARE @n AS INT=1
OPEN c3
WHILE @n<=@@CURSOR_ROWS BEGIN
FETCH ABSOLUTE @n FROM c3 INTO @uid,@cid,@score
PRINT CONVERT(VARCHAR,@uid)+CHAR(9)+CONVERT(VARCHAR,@cid)+CHAR(9)+CONVERT(VARCHAR,@score)
SET @n+=1
END
CLOSE c3
DEALLOCATE c3
GO
